﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ePlanet.ArchiveBorrow.Models;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using DreamBuilder.Core.DAO;
using CommonModule;
using System.Data;
using Web.SystemConfig.WorkFlow;
using Web.SystemConfig.SysCache;

namespace ePlanet.ArchiveBorrow.DataAccess
{
    public class ArchiveBorrowDAL : DataHelper
    {
        public ArchiveBorrowDAL()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("仅支持Oracle数据库");
            }
        }

        public void InsertNewArchiveBorrowModel(ArchiveBorrowModel model)
        {
            this.InsertData(model);
        }

        public void UpdateArchiveBorrowModel(ArchiveBorrowModel model)
        {
            this.InsertData(model);
        }

        public ArchiveBorrowModel GetSingleArchiveBorrowModelByDataID(Guid dataID)
        {
            ArchiveBorrowModel rtnModel = new ArchiveBorrowModel();
            string sql = string.Format("select * from ARCHIVEBORROW where DataID='{0}'", dataID);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnModel.ARCHIVEBORROW.Merge(oDataSet.Tables[0]);
            }

            return rtnModel;
        }

        public ArchiveBorrowModel GetArchiveBorrowModelListByUserID(Guid userID, int statusID, int beginNum, int count, out int sum)
        {
            sum = 0;
            return new ArchiveBorrowModel();
        }

        public ArchiveBorrowModel GetArchiveBorrowModelListByArchiveType(string achiveType, int statusID, int beginNum, int count, out int sum)
        {
            sum = 0;
            return new ArchiveBorrowModel();
        }

        public DataTable GetToUserList(int roleID)
        {
            DataTable rtnDataTable = null;
            string sql = string.Format(@"select u.user_name||'['||o.Org_name||']' as UserName,u.user_id as UserID from p_auth_User_role r
                                                                inner join p_auth_User u
                                                                on r.user_id=u.User_ID
                                                                and r.Role_ID='{0}'
                                                                inner join p_auth_org o
                                                                on u.Org_ID=o.Org_ID", roleID);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0)
            {
                rtnDataTable = oDataSet.Tables[0];
            }
            return rtnDataTable;
        }

        /// <summary>
        /// 获取没有审核通过的借阅
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="beginNum"></param>
        /// <param name="count"></param>
        /// <param name="sum"></param>
        /// <returns></returns>
        public ArchiveBorrowModel GetFaild(string userID, int beginNum, int count, ref int sum)
        {
            ArchiveBorrowModel rtnArchiveBorrowModel = new ArchiveBorrowModel();
            int originalStatusID = new WorkFlowManager().GetOriginalStastuIDByTableID(ArchiveTableEnum.ArchiveBorrow);
            int finishStatusID = new ArchiveCacheFacade().GetStatusRuleCache().GetFinishStatus((int)ArchiveTableEnum.ArchiveBorrow);
            string sql = string.Format(@"select distinct a.* from archiveborrow a
join workflow_operatelog w
on a.DataID=w.RecordID
where a.CreateUserID='{0}'
and a.StatusID ={1}
and a.DataID  in
(
  select distinct a.DataID from archiveborrow a
  join workflow_operatelog w
  on a.DataID=w.RecordID
  where a.CreateUserID='{0}'
  and a.StatusID ={1}
  and w.OperateID in (select operateID from workflow_operaterule where operateid>999 and operatename ='不同意')
) 
and rownum between {2} and {3}", userID, originalStatusID, beginNum, beginNum + count);
            DataSet oDataSet = this.DBHelper.ExecuteDataSet(sql);
            if (oDataSet != null && oDataSet.Tables.Count > 0 && oDataSet.Tables[0].Rows.Count > 0)
            {
                rtnArchiveBorrowModel.ARCHIVEBORROW.Merge(oDataSet.Tables[0]);
            }
            return rtnArchiveBorrowModel;
        }

        /// <summary>
        /// 分页获取待办数据
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="beginNum"></param>
        /// <param name="count"></param>
        /// <param name="sum"></param>
        /// <returns></returns>
        public ArchiveBorrowModel GetAgent(string userID, int beginNum, int count, string filter, ref int sum)
        {
            ArchiveBorrowModel rtnArchiveBorrowModel = new ArchiveBorrowModel();
            DataTable oDataTable = new WorkFlow().GetAgent(ArchiveTableEnum.ArchiveBorrow, userID, beginNum, count, filter, ref  count);
            if (oDataTable != null && oDataTable.Rows.Count > 0)
            {
                DataView oDataView = oDataTable.Copy().DefaultView;
                oDataView.Sort = "validperiod desc";
                rtnArchiveBorrowModel.ARCHIVEBORROW.Merge(oDataView.ToTable());
            }
            return rtnArchiveBorrowModel;
        }

        /// <summary>
        /// 分页获取已办数据
        /// </summary>
        /// <param name="tableEnum"></param>
        /// <param name="userID"></param>
        /// <param name="beginNum"></param>
        /// <param name="count"></param>
        /// <param name="sum"></param>
        /// <returns></returns>
        public ArchiveBorrowModel GetDone(string userID, int beginNum, int count, string filter, ref int sum)
        {
            ArchiveBorrowModel rtnArchiveBorrowModel = new ArchiveBorrowModel();
            DataTable oDataTable = new WorkFlow().GetDone(ArchiveTableEnum.ArchiveBorrow, userID, beginNum, count, filter, ref  count);
            if (oDataTable != null && oDataTable.Rows.Count > 0)
            {
                DataView oDataView = oDataTable.Copy().DefaultView;
                oDataView.Sort = "validperiod desc";
                rtnArchiveBorrowModel.ARCHIVEBORROW.Merge(oDataView.ToTable());
            }
            return rtnArchiveBorrowModel;
        }

        public ArchiveBorrowModel GetTotal(string userID, int beginNum, int count, out int sum)
        {
            sum = 0;
            ArchiveBorrowModel rtnArchiveBorrowModel = new ArchiveBorrowModel();
            DataTable oDataTable = new WorkFlow().GetTotal(ArchiveTableEnum.ArchiveBorrow, beginNum, count, out sum, " validperiod>sysdate", userID);
            if (oDataTable != null && oDataTable.Rows.Count > 0)
            {
                DataView oDataView = oDataTable.Copy().DefaultView;
                oDataView.Sort = "validperiod asc";
                rtnArchiveBorrowModel.ARCHIVEBORROW.Merge(oDataView.ToTable());
            }
            return rtnArchiveBorrowModel;
        }

        public ArchiveBorrowModel GetHasLend(string userID, int beginNum, int count, out int sum)
        {
            sum = 0;
            ArchiveBorrowModel rtnArchiveBorrowModel = new ArchiveBorrowModel();
            DataTable oDataTable = new WorkFlow().GetTotal(ArchiveTableEnum.ArchiveBorrow, beginNum, count, out sum, " validperiod>sysdate and ISPaperArchiveReturn=0 and returntime is null", userID);
            if (oDataTable != null && oDataTable.Rows.Count > 0)
            {
                DataView oDataView = oDataTable.Copy().DefaultView;
                oDataView.Sort = "validperiod asc";
                rtnArchiveBorrowModel.ARCHIVEBORROW.Merge(oDataView.ToTable());
            }
            return rtnArchiveBorrowModel;
        }

        public ArchiveBorrowModel GetHasReturn(string userID, int beginNum, int count, out int sum)
        {
            sum = 0;
            ArchiveBorrowModel rtnArchiveBorrowModel = new ArchiveBorrowModel();
            DataTable oDataTable = new WorkFlow().GetTotal(ArchiveTableEnum.ArchiveBorrow, beginNum, count, out sum, " validperiod>sysdate and ISPaperArchiveReturn=1", userID);
            if (oDataTable != null && oDataTable.Rows.Count > 0)
            {
                DataView oDataView = oDataTable.Copy().DefaultView;
                oDataView.Sort = "validperiod asc";
                rtnArchiveBorrowModel.ARCHIVEBORROW.Merge(oDataView.ToTable());
            }
            return rtnArchiveBorrowModel;
        }

        public string GetMaxOrderID(out uint maxValue)
        {
            string rtnString = string.Empty;
            maxValue = uint.MinValue;
            string sql = string.Format("select max(To_number(orderid)) from archiveborrow");
            object obj = DBHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                if (!uint.TryParse(obj.ToString(), out maxValue))
                {
                    rtnString = "获取最大编号失败！";
                }
            }

            return rtnString;
        }

        /// <summary>
        /// 查询相同订单号的数据
        /// </summary>
        /// <param name="dataID"></param>
        /// <returns></returns>
        public DataTable GetSameOrderIDData(string dataID)
        {
            string sql = string.Format(@"select *
  from archiveborrow
 where orderid in (select orderid from archiveborrow where dataid = '{0}')
", dataID);
            return DBHelper.ExecuteDataSet(sql).Tables[0];
        }

        /// <summary>
        /// 查询相同订单号的数据的操作日志
        /// </summary>
        /// <param name="dataID"></param>
        /// <returns></returns>
        public DataTable GetSameOrderIDDataOperateLog(string dataID)
        {
            string sql = string.Format(@"select *
  from workflow_operatelog
 where RecordID in
       (select DataID
          from archiveborrow
         where orderid in
               (select orderid from archiveborrow where dataid = '{0}'))
   and tableID = 11
   order by RecordID", dataID);
            return DBHelper.ExecuteDataSet(sql).Tables[0];
        }

        public bool IsAllFinish(string orderNo)
        {
            bool rtnBool = false;
            string sql = string.Format("select count(*) from archiveborrow where OrderID='{0}' and statusID not in (505,500)", orderNo);
            object obj = DBHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                ///查询为0条数据，表示都审核完毕
                rtnBool = obj.ToString() == "0" ? true : false;
            }

            return rtnBool;
        }

        public string GetUserOrgID(string userID)
        {
            string sql = string.Format(@"select Org_ID from p_auth_user where User_ID='{0}'", userID);
            object obj = DBHelper.ExecuteScalar(sql);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                throw new Exception("此用户没有组织机构");
            }
        }

        public void PaperArchiveLend(string archiveDataID, DateTime returntime)
        {
            string sql = string.Format("update archiveborrow set ispaperarchivereturn=1,returntime=to_date('{0}', 'yyyy-mm-dd') where dataid='{1}'", returntime.ToShortDateString(), archiveDataID);
            DBHelper.ExecuteSql(sql);
        }

        public void PaperArchiveReturn(string archiveDataID)
        {
            string sql = string.Format("update archiveborrow set ispaperarchivereturn=0 where dataid='{0}'", archiveDataID);
            DBHelper.ExecuteSql(sql);
        }

        //Add By Yidane
        //Date:2014年5月12日01:54:31
        public DataTable QueryAllArchiveBorrowMenu()
        {
            var sql = "select * from archiveborrowtreemenu order by typeid";
            var result = DBHelper.ExecuteDataSet(sql);

            return (result != null && result.Tables.Count > 0) ? result.Tables[0] : null;
        }

        public DataTable GetArvhiceBorrowStatistics(DateTime beginTime, DateTime endTime, int archiveType)
        {
            var sql = string.Format(@"select count(distinct borrowusername || to_char(createtime, 'yyyy-mm-dd'))
  from archiveborrow
 where archivetypeID = {2}
   and createtime between to_date('{0}','yyyy-mm-dd hh24-mi-ss') and to_date('{1}','yyyy-mm-dd hh24-mi-ss')
union all
select count(distinct archivedataid) from archiveborrow
where archivetypeID = {2}
   and createtime between to_date('{0}','yyyy-mm-dd hh24-mi-ss') and to_date('{1}','yyyy-mm-dd hh24-mi-ss')
union all
select count(distinct archivedataid)
  from archiveborrow
 where ispaperarchive = 1
   and archivetypeID = {2}
   and createtime between to_date('{0}','yyyy-mm-dd hh24-mi-ss') and to_date('{1}','yyyy-mm-dd hh24-mi-ss')
union all
select count(distinct archivedataid)
  from archiveborrow
 where ispaperarchive = 1
   and ISPaperArchiveReturn = 0
   and returntime is null
   and archivetypeID = {2}
   and createtime between to_date('{0}','yyyy-mm-dd hh24-mi-ss') and to_date('{1}','yyyy-mm-dd hh24-mi-ss')
union all
select count(distinct archivedataid)
  from archiveborrow
 where ispaperarchive = 1
   and ISPaperArchiveReturn = 1
   and archivetypeID = {2}
   and createtime between to_date('{0}','yyyy-mm-dd hh24-mi-ss') and to_date('{1}','yyyy-mm-dd hh24-mi-ss')", beginTime, endTime, archiveType);

            var result = DBHelper.ExecuteDataSet(sql);

            return (result != null && result.Tables.Count > 0) ? result.Tables[0] : null;
        }
    }
}
